﻿/******************************************************************
** 文 件 名: BD_Contract_DAL.cs
** Copyright (c) 2010-2012 Honestysoft (GuangZhou) Co.,LTD
** 创 建 人: 此代码由生成器自动生成
** 创建日期: 2012-3-8 2:35:56
** 描    述: 实现公共部分的数据访问封装，提供基础资料-合同信息的数据访问类
** 版    本: 2.0.0.0
** 修改描述:
** 修 改 人:
** 修改日期:
******************************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using Honestysoft.BaseObject;
using Honestysoft.Financing.Model;

namespace Honestysoft.Financing.DataAccess
{
	/// <summary>
    /// BD_Contract数据访问类
    /// </summary>
	public partial class BD_Contract_DAL : BaseDataAccess<BD_Contract>
    {		
		/// <summary>
        /// 构造函数，使用指定的连接字符串
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称</param>
        public BD_Contract_DAL(string connectionStringName)
            : base(connectionStringName)
        {

        }

        public DataTable GetChildNode(string parentId)
        { 
            DbParameter parameter = CreateParameter("ParentID");
            parameter.DbType = DbType.String;
            parameter.Value  =parentId;
            string sql = string.Format(@"Select A.ID,A.ParentID,A.NodeText,Count(B.ID) ChildNodes
                From V_ContractTree A Left Join V_ContractTree B On A.ID=B.ParentID Where A.ParentID={0}
                Group by A.ID,A.ParentID,A.NodeText", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            return ExecuteDataTable(command);
        }

        public DataTable GetRootNode()
        {
            string sql = @"Select A.ID,A.ParentID,A.NodeText,Count(B.ID) ChildNodes
                From V_ContractTree A Left Join V_ContractTree B On A.ID=B.ParentID Where A.ParentID IS NULL
                Group by A.ID,A.ParentID,A.NodeText";
            DbCommand command = GetDbCommand(sql);
            return ExecuteDataTable(command);
        }
        
        public DataTable GetContracts(string projectCategory,string projectId,string projectName, string category, string code, string name, int pageSize, int currentPageIndex, out int pageCount, out int recordCount)
        {
            DbParameter pProjectCategory = CreateParameter("ProjectCategory");
            pProjectCategory.DbType = DbType.String;
            pProjectCategory.Value = projectCategory;
            DbParameter pProject = CreateParameter("ProjectID");
            pProject.DbType = DbType.String;
            pProject.Value = projectId;
            DbParameter pProjectName = CreateParameter("SN");
            pProjectName.DbType = DbType.String;
            pProjectName.Value = projectName;
            DbParameter pCategory = CreateParameter("ContractCategory");
            pCategory.DbType = DbType.String;
            pCategory.Value = category;
            DbParameter pCode = CreateParameter("Code");
            pCode.DbType = DbType.String;
            pCode.Value = code;
            DbParameter pName = CreateParameter("Name");
            pName.DbType = DbType.String;
            pName.Value = name;
            string sql = string.Format(@"Select A.*,B.ProjectCategory,B.SortCode
                From BD_Contract A Join BD_Project B
                On A.ProjectID=B.ID Where 1=1 {0} {1} {2} {3} {4} {5}",
                                                          string.IsNullOrEmpty(category) ? "" : string.Format(" AND A.ContractCategory={0}", pCategory.ParameterName),
                                                          string.IsNullOrEmpty(code) ? "" : string.Format(" AND A.Code LIKE '%'+{0}+ '%' ", pCode.ParameterName),
                                                          string.IsNullOrEmpty(name) ? "" : string.Format(" AND A.Name LIKE '%'+{0}+ '%' ", pName.ParameterName),
                                                          string.IsNullOrEmpty(projectCategory) ? "" : string.Format(" AND B.ProjectCategory={0}",pProjectCategory.ParameterName),
                                                          string.IsNullOrEmpty(projectId) ? "" : string.Format(" AND A.ProjectID={0}",pProject.ParameterName),
                                                          string.IsNullOrEmpty(projectName) ? "" : string.Format(" And B.SN LIKE '%'+{0}+ '%' ",pProjectName.ParameterName));
            DbCommand command = GetDbCommand(sql);
            if (!string.IsNullOrEmpty(category)) { command.Parameters.Add(pCategory); }
            if (!string.IsNullOrEmpty(code)) { command.Parameters.Add(pCode); }
            if (!string.IsNullOrEmpty(name)) { command.Parameters.Add(pName); }
            if (!string.IsNullOrEmpty(projectCategory)) { command.Parameters.Add(pProjectCategory); }
            if (!string.IsNullOrEmpty(projectId)) { command.Parameters.Add(pProject); }
            if (!string.IsNullOrEmpty(projectName)) { command.Parameters.Add(pProjectName); }
            DataTable dt = ExecuteDataTable(command, "SortCode,Code", pageSize, currentPageIndex, out pageCount, out recordCount);
            dt.Columns.Add("IsUse",typeof(bool));
            if (dt != null && dt.Rows.Count > 0)
            {
                string idString = "";
                foreach (DataRow dr in dt.Rows)
                {
                    if (idString != "") { idString += ","; }
                    idString += "'" + dr["ID"].ToString() + "'";
                }
                string sql2 = @"Select A.ContractID,Sum(A.useCount)  useCount From (
                SELECT COUNT(ID) useCount,ContractID FROM Bu_DocReceive_Temp
                Group by CONTRACTID
                Union 
                SELECT COUNT(ID),CONTRACTID FROM Bu_DocReceive
                Group by CONTRACTID) A
                Where IsNull(A.ContractID,'')<>''
                And A.ContractID in (" + idString + ") Group by A.ContractID";
                DataTable dt2 = ExecuteDataTable(sql2);
                DataRow[] rows = null;
                foreach (DataRow dr in dt.Rows)
                {
                    dr["IsUse"] = false;
                    rows = dt2.Select("ContractID='" + dr["ID"].ToString() + "'");
                    if (rows != null && rows.Length == 1)
                    {
                        dr["IsUse"] = Convert.ToInt32(rows[0]["useCount"]) > 0;
                    }
                }
            }
            return dt;
        }

        public bool ConfirmInit(string[] arrId, string userId, DateTime confirmTime, string opinion)
        {
            List<DbParameter> pIDList = GetDbParametersList("ID", arrId);
            DbParameter pUserID = GetDbParameter("ConfirmUser", userId);
            DbParameter pTime = GetDbParameter("ConfirmTime", confirmTime);
            DbParameter pOpinion = GetDbParameter("ConfirmOpinion", opinion);
            DbParameter pIsInit = GetDbParameter("IsInit", true);
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"UPDATE BD_Contract Set IsInit={0}, ConfirmUser={1},ConfirmTime={2},ConfirmOpinion={3} WHERE ID ",
               pIsInit.ParameterName, pUserID.ParameterName, pTime.ParameterName, pOpinion.ParameterName);
            if (pIDList.Count == 1)
            {
                sb.AppendFormat("={0}", pIDList[0].ParameterName);
            }
            else
            {
                sb.Append(" IN (");
                foreach (DbParameter p in pIDList)
                {
                    sb.Append(p.ParameterName);
                    sb.Append(",");
                }
                sb.Remove(sb.Length - 1, 1);
                sb.Append(")");
            }
            DbCommand command = GetDbCommand(sb.ToString());
            command.Parameters.Add(pIsInit);
            command.Parameters.Add(pUserID);
            command.Parameters.Add(pTime);
            command.Parameters.Add(pOpinion);
            foreach (DbParameter p in pIDList)
            {
                command.Parameters.Add(p);
            }
            return ExecuteNonQuery(command) == arrId.Length;
        }

        /// <summary>
        /// 得到合同信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public BD_Contract GetContract(string id)
        {
            DbParameter parameter = CreateParameter("id");
            parameter.DbType = DbType.String;
            parameter.Value = id;
            DbCommand command = GetStoredProcCommand("Proc_GetContract");
            command.Parameters.Add(parameter);
            DataTable dt = ExecuteDataTable(command);
            List<BD_Contract> lsContract = Honestysoft.BaseObject.DataMapping.TableToEntity<BD_Contract>(dt);
            if (lsContract == null || lsContract.Count != 1) { return null; }
            lsContract[0].ContractorName = dt.Rows[0]["ContractorName"].ToString();
            lsContract[0].ProjectName = dt.Rows[0]["ProjectName"].ToString();
            lsContract[0].HasChangeRecord = Convert.ToBoolean(dt.Rows[0]["HasChangeRecord"]);
            lsContract[0].CurChangePrice = Convert.ToDecimal(dt.Rows[0]["CurChangePrice"]);
            lsContract[0].LastChangeDate = Convert.ToDateTime(dt.Rows[0]["LastChangeDate"]);
            lsContract[0].ProjectCode = dt.Rows[0]["ProjectCode"].ToString();
            lsContract[0].OwnerName = dt.Rows[0]["OwnerName"].ToString();
            lsContract[0].Owner971Name = dt.Rows[0]["Owner971Name"].ToString();
            lsContract[0].Owner = dt.Rows[0]["Owner"].ToString();
            lsContract[0].Owner971 = dt.Rows[0]["Owner971"].ToString();
            lsContract[0].DBankCompanyName = dt.Rows[0]["DBankCompanyName"].ToString();
            return lsContract[0];
        }

        public DataTable GetContracts(string projectId, string contractCategory, bool? isInit)
        {
            DbParameter pProjectID=GetDbParameter("ProjectID",projectId);
            DbParameter pContractCategory = GetDbParameter("ContractCategory",contractCategory);
            DbParameter pIsInit=CreateParameter("IsInit");
            pIsInit.DbType = DbType.Boolean;
            string sql = string.Format(@"Select ID,ProjectID,ContractCategory,Code,Name,IsInit
                        From BD_Contract
                        Where ProjectID={0} And ContractCategory={1} {2} Order by Code", 
                                                 pProjectID.ParameterName, pContractCategory.ParameterName,
                                                 isInit.HasValue ? string.Format(" And IsInit={0}", pIsInit.ParameterName) : "");
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pProjectID);
            command.Parameters.Add(pContractCategory);
            if (isInit.HasValue)
            {
                pIsInit.Value = isInit.Value;
                command.Parameters.Add(pIsInit);
            }
            return ExecuteDataTable(command);
        }

        public DataTable GetContractCategoryCount(string projectId, bool? isInit)
        {
            DbParameter pProjectID=GetDbParameter("ProjectID",projectId);
            DbParameter pIsInit=CreateParameter("IsInit");
            pIsInit.DbType = DbType.Boolean;
            string sql = string.Format(@"Select ContractCategory,Count(ID) ContractCount From BD_Contract
                    Where ProjectID={0} {1}
                    Group by ContractCategory", pProjectID.ParameterName,
                          isInit.HasValue ? string.Format(" And IsInit={0}", pIsInit.ParameterName) : "");
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pProjectID);
            if (isInit.HasValue)
            {
                pIsInit.Value = isInit.Value;
                command.Parameters.Add(pIsInit);
            }
            return ExecuteDataTable(command);
        }

        public DataTable GetContractProject(string[] arrId)
        {
            if (arrId == null || arrId.Length==0) { return null; }
            List<DbParameter> pIDList = GetDbParametersList("ID", arrId);
            StringBuilder sb = new StringBuilder();
            sb.Append(@"Select A.ID ContractID,A.Name ContractName,
                B.ID ProjectID,B.Name ProjectName
                From BD_Contract A 
                Inner Join BD_Project B On A.ProjectID=B.ID Where A.ID ");
            if (pIDList.Count == 1)
            {
                sb.AppendFormat("={0}", pIDList[0].ParameterName);
            }
            else
            {
                sb.Append(" IN (");
                foreach (DbParameter p in pIDList)
                {
                    sb.Append(p.ParameterName);
                    sb.Append(",");
                }
                sb.Remove(sb.Length - 1, 1);
                sb.Append(")");
            }
            DbCommand command = GetDbCommand(sb.ToString());
            foreach (DbParameter p in pIDList)
            {
                command.Parameters.Add(p);
            }
            return ExecuteDataTable(command);
        }

        public bool IsExistsCode(string code)
        {
            DbParameter parameter = GetDbParameter("Code",code);
            string sql = string.Format(@"Select Count(ID) From BD_Contract Where Code={0}", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            object obj = ExecuteScalar(command);
            if (obj == null || obj == DBNull.Value)
            {
                return false;
            }
            return Convert.ToInt32(obj) > 0;
        }

        public bool IsExistsName(string name)
        {
            DbParameter parameter = GetDbParameter("Name", name);
            string sql = string.Format(@"Select Count(ID) From BD_Contract Where Name={0}", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            object obj = ExecuteScalar(command);
            if (obj == null || obj == DBNull.Value)
            {
                return false;
            }
            return Convert.ToInt32(obj) > 0;
        }

        /// <summary>
        /// 得到相似的合同（使用价格）
        /// </summary>
        /// <param name="price"></param>
        /// <returns></returns>
        public DataTable GetLikeContractByPrice(decimal price)
        {
            string sql = @"SELECT ID,Code,Name,TotalPrice
                FROM BD_Contract
                where  totalPrice=" + Convert.ToString(price);
            
            return ExecuteDataTable(sql);
        }

        /// <summary>
        /// 得到相似的合同（使用名称）
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataTable GetLikeContractByName(string name)
        {
            DbParameter parameter = CreateParameter("Name");
            parameter.DbType = DbType.String;
            parameter.Value = name;
            DbCommand command = GetStoredProcCommand("Get_LikeContract");
            command.Parameters.Add(parameter);
            return ExecuteDataTable(command);
        }

        public string GetNextContractCode(string projectId)
        {
            DbParameter parameter = GetDbParameter("ProjectID",projectId);
            string sql=string.Format("Select Max(Code) From BD_Contract Where ProjectID={0}",parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            object obj = ExecuteScalar(command);
            if (obj == null || obj == DBNull.Value)
            {
                DbParameter pID = CreateParameter("ID");
                pID.DbType = DbType.String;
                pID.Value = projectId;
                sql = string.Format("Select FCode From BD_Project Where ID={0}", pID.ParameterName);
                command = GetDbCommand(sql);
                command.Parameters.Add(pID);
                obj = ExecuteScalar(command);
                if (obj == null || obj == DBNull.Value)
                {
                    return "";
                }
                else
                {
                    return obj.ToString() +"-C001";
                }
            }
            else
            {
                string maxCode = obj.ToString();
                string left = maxCode.Substring(0, maxCode.Length - 3);
                string right = maxCode.Substring(maxCode.Length - 3);
                maxCode = left  + (Convert.ToInt32(right) + 1).ToString().PadLeft(3, '0');
                return maxCode;
            }
        }

        /// <summary>
        /// 刷新作业表的合同信息
        /// </summary>
        /// <param name="projectId"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int RefreshATContractInfo(string contractId, DbTransaction transaction)
        {
            DbParameter parameter = CreateParameter("ContractID");
            parameter.DbType = DbType.String;
            parameter.Value = contractId;
            DbCommand command = GetStoredProcCommand("Proc_RefreshATContractInfo");
            command.Parameters.Add(parameter);
            return ExecuteNonQuery(command, transaction);
        }
    }
}
